---
title: "SQL-020: Future cancel date"
summary: Retrieve the pending cancellation date for the latest unreinstated policy cancellation.
status: draft
owner: SAIF Corporation
last_reviewed: 2026-08-21
library: SAIF.Business.View.Policy
reference_type: embedded
---

# SQL-020 Future cancel date

This SQL retrieves the latest unreinstated and unrescinded cancellation date for a policy so pending cancellation status can expose the future cancel date.

## Classification

| Field | Value |
| --- | --- |
| **SQL ID** | SQL-020 |
| **Operation type** | Read |
| **Execution method** | Inline |
| **Database** | Oracle |
| **Schema** | `[unqualified]**` |
| **Command type** | Text |
| **Business object** | `clsPolicyData` |
| **Criteria property** | `N/A` |
| **Lifecycle method** | `GetFutureCancelDate` |

## Parameters

| Parameter | Direction | Type | Max length | Description |
| --- | --- | --- | --- | --- |
| `:PolicyNumber` | Input | Decimal | N/A | Policy number whose future cancellation date is requested. |

## Tables

| Table | Access | Columns used |
| --- | --- | --- |
| [CANCELLATION](../database-tables.md#cancellation) | SELECT | `policy_num`, `cancellation_seq_num`, `cancel_date`, `reinstate_date`, `rescind_date |

## Sample SQL

```sql
SELECT
  cancel_date
FROM cancellation c1
WHERE (policy_num = :PolicyNumber)
  AND cancellation_seq_num = (
    SELECT
      MAX(cancellation_seq_num)
    FROM cancellation c2
    WHERE c1.policy_num = c2.policy_num)
  AND reinstate_date IS NULL
  AND rescind_date IS NULL
```

## Operations and processes
- [Retrieve policy data](../operations-processes.md)

## Business rules
- [BR-VP-017: Future cancel date only for Pending Cancellation](../business-rules.md#br-vp-017-future-cancel-date-only-for-pending-cancellation)

## Sources
- `SAIF.Business.View.Policy/clsPolicyData.vb`